package ems.dev.easycode.util;

import cn.hutool.core.util.StrUtil;
import ems.dev.easycode.domain.entity.ColumnEntity;
import ems.dev.easycode.domain.entity.TableEntity;
import java.sql.*;
import java.util.*;

/**
 * @author 狠穿
 * @version 1.0.0
 * @date 2023/5/18
 * @function 封装DB2相关操作
 */
public class Db2Util {


    /**
     * db2连接配置
     */
    private static final String driver = PropertiesUtil.getProValueStr(Constant.EASY_CODE_PROPERTIES_FILE_NAME,"jdbc.driver");
    private static final String url = PropertiesUtil.getProValueStr(Constant.EASY_CODE_PROPERTIES_FILE_NAME,"jdbc.url");
    private static final String username = PropertiesUtil.getProValueStr(Constant.EASY_CODE_PROPERTIES_FILE_NAME,"jdbc.username");
    private static final String password =PropertiesUtil.getProValueStr(Constant.EASY_CODE_PROPERTIES_FILE_NAME,"jdbc.password");


    /**
     * db2 连接对象
     */
    private static Connection connection= null ;

    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 自动检测url，db2中url必须以分号结束才能正确进行连接
     * @param url
     * @return
     */
    private static String finalUlr(String url){
        return url.endsWith(";")?url:url+";";
    }

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConn(){
        if(connection==null){
            try {
                connection = DriverManager.getConnection(finalUlr(url),username,password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    /**
     * 基于schema和表名，获取表的相关信息
     * @param schema
     * @param table
     * @return
     */
    public static TableEntity getTableInfo(String schema,String table) throws SQLException {
        Connection conn = getConn();
        //表信息
        ResultSet tableResultSet = conn.getMetaData().getTables(conn.getCatalog(), schema, table, null);
        TableEntity tableEntity = new TableEntity();
        if(tableResultSet.next()){
            tableEntity.setSchema(tableResultSet.getString("TABLE_SCHEM"));
            tableEntity.setName(tableResultSet.getString("TABLE_NAME"));
            tableEntity.setComment(tableResultSet.getString("REMARKS"));
        }
        //表的主键
        Set<String> primaryKeySet = new HashSet<>();
        ResultSet primaryKeys = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), schema, table);
        while (primaryKeys.next()){
            String primaryKeyName = primaryKeys.getString("COLUMN_NAME");
            primaryKeySet.add(primaryKeyName);
        }
        //字段信息
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(StrUtil.format("SELECT * FROM {}.{} FETCH FIRST 1 ROWS ONLY",schema,table));
        //字段类型的Map容器
        Map columnTypeMap = new HashMap();
        for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
            ResultSetMetaData metaData = resultSet.getMetaData();
            String columnName = metaData.getColumnName(i+1);
            String columnClassName = metaData.getColumnClassName(i+1);
            columnTypeMap.put(columnName,columnClassName);
        }
        //对应的页面号:有页面号则优先采用页面号生成相关内容，没有的时候取表名
        String pageNo = MappingUtil.getPageNo(tableEntity.getName());
        if(StrUtil.isBlank(pageNo)){
            pageNo = tableEntity.getName();
        }
        tableEntity.setPageNo(pageNo);
        ResultSet columnsResultSet = conn.getMetaData().getColumns(conn.getCatalog(), schema, table, Constant.ALL_COLUMN_LIKE);
        List<ColumnEntity> columnList = new ArrayList<>();
        while(columnsResultSet.next()){
            ColumnEntity columnEntity = new ColumnEntity();
            String columnName = columnsResultSet.getString("COLUMN_NAME");
            columnEntity.setColumn(columnName); //COLUMN_NAME
            columnEntity.setColumnType(columnsResultSet.getString("TYPE_NAME")); //TYPE_NAME
            columnEntity.setLength(columnsResultSet.getString("COLUMN_SIZE")); //COLUMN_SIZE
            columnEntity.setComment(columnsResultSet.getString("REMARKS")); // REMARKS
            columnEntity.setNullable(columnsResultSet.getBoolean("NULLABLE")); // NULLABLE
            //按照key从map中去取对应的java类型
            columnEntity.setJavaType(columnTypeMap.get(columnName).toString());
            //从主键集合中判断该字段是否是主键，集合中存在则是，否咋否则不是
            columnEntity.setPk(primaryKeySet.contains(columnName));
            columnEntity.setFieldName(FormatUtil.dbColumnToLowerCamelCase(columnName));
            columnList.add(columnEntity);
        }
        tableEntity.setColumns(columnList);
        return tableEntity;
    }

    /**
     * 基于字段列表，生成取别名的通用sql片段
     * @param columns
     * @return
     */
    public static String aliasForColumns( List<ColumnEntity> columns){
        StringBuilder str = new StringBuilder();
        for (int i = 0; i < columns.size(); i++) {
            ColumnEntity columnEntity = columns.get(i);
            str.append(columnEntity.getColumn());
            str.append(" AS ");
            str.append("\"");
            str.append(FormatUtil.dbColumnToLowerCamelCase(columnEntity.getColumn()));
            str.append("\"");
            if(i != columns.size()-1){
                str.append(",");
            }
            str.append("\n");
        }
        return str.toString();
    }

    public static void main(String[] args) {
        try {
            TableEntity tableInfo = getTableInfo("IPLATHB", "T_HA_DISCHARGE_GAS");
            System.out.println(tableInfo);
            System.out.println(getCurSchema());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static String getCurSchema(){
        String url = PropertiesUtil.getProValueStr(Constant.EASY_CODE_PROPERTIES_FILE_NAME, "jdbc.url");
        String curSchema = url.split("currentSchema=")[1];
        return curSchema;
    }


}
